Stored data under management of a database management system typically may be read in two ways. The first way of reading data is read-only which is commonly utilized by users, human and logical, for reporting purposes. No exclusive locks on data are created when data is read in a read-only fashion. The other way of reading data is read-for-update. When a read-for-update is performed, an exclusive lock is placed on at least the affected data, if not the entire rows or pages of data from which the data is selected. In such read-for-update instances, other users may not access the exclusively locked data until a transaction of the read-for-update is terminated, such as through a commit or rollback. Exclusive locking of data prevents not only other read-for-update transactions, but also read-only transactions.
As a result of data and row locking caused by read-for-update transactions, database latency may become an issue. Although systems accessing data in a database management system may be able to perform operations on data quickly, the database may not be able to keep pace in providing needed data when the data is locked. Thus, database latency is often a root cause of overall system latency.
Multi-version concurrency control (MVCC) provides a solution. MVCC systems maintain multiple versions of a record in a database management system and provide concurrent access to the database. Each user is provided with a “snapshot” of the database. Any changes made within that snapshot are not seen by other users of the database until the transaction has been committed.
MVCC uses time stamping, incremental transaction IDs, or other similar solutions to ensure a transaction does not need to wait for a locked record or other database objects by maintaining several versions of the record. Each version is given a relational identifier, such as a timestamp, to track relations between the concurrent data snapshots. A transaction is allowed to read the most recent committed version of a record that precedes its relational identifier.
Each time a record is edited and the change is posted to the database, the database management system creates a new version of the record. All other transactions operating with the record continue see the old version as long as they do not commit. Accordingly, a user accessing a given record sees the most recently committed version of the record. However, the user cannot access a newer version of the record as modified in an open, uncommitted read-for-update transaction. Such newer record versions are enclosed and isolated within their respective transactions until committed.